package org.kzwl.comm.utils;

import java.io.*;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import com.monitorjbl.xlsx.StreamingReader;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class PdfUtils {

    public static void main(String[] args) throws Exception {
        String filePath = "C:\\Users\\Administrator\\Desktop\\pdf\\32万目录.xlsx";
//        String columns[] = {"id", "一级名称", "二级名称", "书名", "作者", "出版社", "isbn", "日期", "封面路径", "文件路径"};
        FileInputStream in = new FileInputStream(new File(filePath));
        Workbook workbook = StreamingReader.builder()
                .rowCacheSize(100)//一次读取多少行(默认是10行)
                .bufferSize(1024)//使用的缓冲大小(默认1024)
                .open(in);
        for (Sheet sheet : workbook) {
            for (Row row : sheet) {
                System.out.println("分类" + row.getCell(1).getStringCellValue());
                System.out.println("书名" + row.getCell(3).getStringCellValue());
                System.out.println("作者" + row.getCell(4).getStringCellValue());
                System.out.println("出版社" + row.getCell(5).getStringCellValue());
                String a = row.getCell(8).getStringCellValue();
                StringBuffer str = new StringBuffer(a);
                a = str.insert(a.indexOf("/"), "11").toString();
                System.out.println("封面路径" + a);
                String b = row.getCell(9).getStringCellValue();
                str = new StringBuffer(b);
                b = str.insert(b.indexOf("/"), "11").toString();
                System.out.println("文件路径" + b);
                System.out.println();
            }
        }

    }

    //读取excel
    public static Workbook readExcel(String filePath) {
        Workbook wb = null;
        if (filePath == null) {
            return null;
        }
        String extString = filePath.substring(filePath.lastIndexOf("."));
        InputStream is = null;
        try {
            is = new FileInputStream(filePath);
            if (".xls".equals(extString)) {
                return wb = new HSSFWorkbook(is);
            } else if (".xlsx".equals(extString)) {
                return wb = new XSSFWorkbook(is);
            } else {
                return wb = null;
            }

        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return wb;
    }

    public static Object getCellFormatValue(Cell cell) {
        Object cellValue = null;
        if (cell != null) {
            //判断cell类型
            switch (cell.getCellType()) {
                case Cell.CELL_TYPE_NUMERIC: {
                    cellValue = String.valueOf(cell.getNumericCellValue());
                    break;
                }
                case Cell.CELL_TYPE_FORMULA: {
                    //判断cell是否为日期格式
                    if (DateUtil.isCellDateFormatted(cell)) {
                        //转换为日期格式YYYY-mm-dd
                        cellValue = cell.getDateCellValue();
                    } else {
                        //数字
                        cellValue = String.valueOf(cell.getNumericCellValue());
                    }
                    break;
                }
                case Cell.CELL_TYPE_STRING: {
                    cellValue = cell.getRichStringCellValue().getString();
                    break;
                }
                default:
                    cellValue = "";
            }
        } else {
            cellValue = "";
        }
        return cellValue;
    }

}
